﻿using System.Xml.Linq;
using DataAccess.SQL;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using System;
using System.Web;
using CommonLib;
using Framework.Caching;
using Framework;
using Entities;
using Entities.System;

namespace BLL.System
{
    public class GroupBO : BusinessBase
    {
        DataTable tblMyAction, tblLoginerAction;
        public string LoginerFunkeys { get; set; }
        public string getSubGroup(Dictionary<string, string> dicLike,
            Dictionary<string, string> dicEqual, int? pageSize, int? pageNum)
        {

            List<Group> lst = new List<Group>();
            var condition = getCondition(dicLike, dicEqual);
            string sql = string.Empty;
            User user = HttpContext.Current.Session["user"] as User;
            if (pageSize.HasValue && pageNum.HasValue)
            {
                int from = pageSize.Value * (pageNum.Value - 1);
                int to = pageSize.Value * pageNum.Value;
                sql = string.Format(@"SELECT *  FROM (SELECT tt.*, ROWNUM AS rowno
                                            FROM ( SELECT  distinct g.group_key,g.group_id,g.group_name , g.group_desc
                                                    FROM SBRM_GROUP  g  left JOIN SBRM_GROUP_FUNCTION gf on g.group_key=gf.group_key
                                                    left join sbrm_group_factory gfa on g.group_key=gfa.group_key 
                                                    where g.ACTIVE='Y' and (  gf.function_key is null  or not  exists(
                                                    select  gff.FUNCTION_KEY from  SBRM_GROUP gg left JOIN SBRM_GROUP_FUNCTION gff on gg.group_key=gff.group_key
                                                    left join 
                                                    (
                                                    select distinct FUNCTION_KEY,ACTION_ID  from SBRM_GROUP_FUNCTION 
                                                    WHERE GROUP_KEY IN (SELECT sg.GROUP_KEY FROM SBRM_USER_GROUP sug join SBRM_GROUP sg on sug.group_key=sg.group_key 
                                                    WHERE   USER_KEY ={3} and sg.ACTIVE='Y' ) ) pg  on gff.function_key=pg.function_key   and gff.action_id=pg.action_id
                                                    where gg.group_key=g.group_key  and pg.function_key is null) )
                                                     and  ( gfa.fac_key is null or not exists (
                                                     select  gffa.FAC_KEY from  SBRM_GROUP ggg left JOIN SBRM_GROUP_FACTORY gffa on ggg.group_key=gffa.group_key
                                                    left join 
                                                    (
                                                    select distinct FAC_KEY from SBRM_GROUP_FACTORY
                                                    WHERE GROUP_KEY IN (SELECT sg.GROUP_KEY FROM SBRM_USER_GROUP sug join SBRM_GROUP sg on sug.group_key=sg.group_key 
                                                    WHERE   USER_KEY ={3} and sg.ACTIVE='Y'  ) ) pf on gffa.fac_key=pf.fac_key  
                                                    where ggg.group_key=g.group_key  and pf.fac_key is null) )  {2}                                                    
                                            ORDER BY GROUP_ID ) tt
                                            WHERE ROWNUM <= {0}) table_alias
                                      WHERE table_alias.rowno >{1} ", to, from, condition, user.USER_KEY);
            }
            else
            {
                sql = string.Format(@"SELECT  distinct g.group_key,g.group_id,g.group_name , g.group_desc
                                    FROM SBRM_GROUP  g  left JOIN SBRM_GROUP_FUNCTION gf on g.group_key=gf.group_key
                                    left join sbrm_group_factory gfa on g.group_key=gfa.group_key 
                                    where g.ACTIVE='Y' and (  gf.function_key is null or  not  exists(
                                    select  gff.FUNCTION_KEY from  SBRM_GROUP gg left JOIN SBRM_GROUP_FUNCTION gff on gg.group_key=gff.group_key
                                    left join 
                                    (
                                    select distinct FUNCTION_KEY,ACTION_ID  from SBRM_GROUP_FUNCTION 
                                    WHERE GROUP_KEY IN (SELECT sg.GROUP_KEY FROM SBRM_USER_GROUP sug join SBRM_GROUP sg on sug.group_key=sg.group_key 
                                    WHERE   USER_KEY ={1} and sg.ACTIVE='Y' ) ) pg  on gff.function_key=pg.function_key   and gff.action_id=pg.action_id
                                    where gg.group_key=g.group_key  and pg.function_key is null) )
                                     and ( gfa.fac_key is null or not exists (
                                     select  gffa.FAC_KEY from  SBRM_GROUP ggg left JOIN SBRM_GROUP_FACTORY gffa on ggg.group_key=gffa.group_key
                                    left join 
                                    (
                                    select distinct FAC_KEY from SBRM_GROUP_FACTORY
                                    WHERE GROUP_KEY IN (SELECT sg.GROUP_KEY FROM SBRM_USER_GROUP sug join SBRM_GROUP sg on sug.group_key=sg.group_key 
                                    WHERE   USER_KEY ={1} and sg.ACTIVE='Y'  ) ) pf on gffa.fac_key=pf.fac_key  
                                    where ggg.group_key=g.group_key  and pf.fac_key is null))   {0}                                           
                                    ORDER BY g.GROUP_ID", condition, user.USER_KEY);
            }
            try
            {
                lst = SqlHelper.ExecuteList<Group>(sql, CommandType.Text);
                //总条数
                sql = string.Format(@"  SELECT  count(distinct g.group_key)
                                        FROM SBRM_GROUP  g  left JOIN SBRM_GROUP_FUNCTION gf on g.group_key=gf.group_key
                                        left join sbrm_group_factory gfa on g.group_key=gfa.group_key 
                                        where  g.ACTIVE='Y' and (  gf.function_key is null or  not  exists(
                                        select  gff.FUNCTION_KEY from  SBRM_GROUP gg left JOIN SBRM_GROUP_FUNCTION gff on gg.group_key=gff.group_key
                                        left join 
                                        (
                                        select distinct FUNCTION_KEY,ACTION_ID  from SBRM_GROUP_FUNCTION 
                                        WHERE GROUP_KEY IN (SELECT sg.GROUP_KEY FROM SBRM_USER_GROUP sug join SBRM_GROUP sg on sug.group_key=sg.group_key 
                                        WHERE   USER_KEY ={1} and sg.ACTIVE='Y' ) ) pg  on gff.function_key=pg.function_key   and gff.action_id=pg.action_id
                                        where gg.group_key=g.group_key  and pg.function_key is null) )
                                            and ( gfa.fac_key is null or not exists (
                                            select  gffa.FAC_KEY from  SBRM_GROUP ggg left JOIN SBRM_GROUP_FACTORY gffa on ggg.group_key=gffa.group_key
                                        left join 
                                        (
                                        select distinct FAC_KEY from SBRM_GROUP_FACTORY
                                        WHERE GROUP_KEY IN (SELECT sg.GROUP_KEY FROM SBRM_USER_GROUP sug join SBRM_GROUP sg on sug.group_key=sg.group_key 
                                        WHERE   USER_KEY ={1} and sg.ACTIVE='Y'  ) ) pf on gffa.fac_key=pf.fac_key  
                                        where ggg.group_key=g.group_key  and pf.fac_key is null))   {0}", condition, user.USER_KEY);
                object oCount = SqlHelper.ExecuteScalar(sql, CommandType.Text);
                int total = Convert.ToInt32(oCount);
                return (new Result<Group> { rows = lst, total = total }).ToJson();

            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();

            }

        }

        /// <summary>获得所有群组
        ///
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNum"></param>
        /// <returns></returns>
        public string getGroup(Dictionary<string, string> dicLike,
            Dictionary<string, string> dicEqual, int? pageSize, int? pageNum)
        {
            List<Group> lst = new List<Group>();
            var condition = getCondition(dicLike, dicEqual);
            string strSql = string.Empty;
            if (pageSize.HasValue && pageNum.HasValue)
            {
                int from = pageSize.Value * (pageNum.Value - 1);
                int to = pageSize.Value * pageNum.Value;
                strSql = @"
select top {1} * from sbrm_group where group_key > (
	select ISNULL(MAX(group_key), 0) from (
		select top {0} * from sbrm_group where Active = 'Y' {2} order by group_key
	) a
) and Active = 'Y' {2} order by GROUP_key ";
                strSql = string.Format(strSql, from, pageSize, condition);
            }
            else
            {
                strSql = string.Format(@"SELECT g.* FROM SBRM_GROUP g where g.Active = 'Y' {0} ORDER BY g.GROUP_ID", condition);
            }
            try
            {
                lst = SqlHelper.ExecuteList<Group>(strSql, CommandType.Text);
                //总条数
                strSql = string.Format("  select count(*)  as rowsCount from SBRM_GROUP g where g.Active = 'Y' {0}", condition);
                object oCount = SqlHelper.ExecuteScalar(strSql, CommandType.Text);
                int total = Convert.ToInt32(oCount);
                return (new Result<Group> { rows = lst, total = total }).ToJson();

            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();

            }

        }

        /// <summary>
        /// 还原群组
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string revertGroup(HttpContext context)
        {
            string strSql = string.Empty;
            int result = 0;
            //还原
            if (!string.IsNullOrEmpty(context.Request.Form["id"])) 
                strSql = string.Format("update  SBRM_GROUP  set active='Y' where  GROUP_ID = '{0}' ", context.Request.Form["id"]);
            result = SqlHelper.ExecuteNonQuery(strSql, CommandType.Text);
            if (result > 0) 
                return (new Status { Success = true }).ToJson();
            else 
                return (new Status { Success = false, Message = "执行异常!" }).ToJson();

        }

        /// <summary>
        /// 删除群组
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string delGroup(HttpContext context)
        {

            string sql = string.Empty;
            string Msg = string.Empty;
            List<string> sqlArr = new List<string>();
            Group group = JsonHelper.FromJson<Group>(context.Request.Form["data"]);
            //删除
            sqlArr.Add(string.Format("update  SBRM_GROUP  set active = 'N'  Where GROUP_KEY = '{0}'", group.GROUP_KEY));
            if (SqlHelper.ExecuteSqlTran(sqlArr))
            {
                //记录日志
                int result = insertLog(context.Session["user"] as User, group, "UPDATE", null);
                return (new Status { Success = true }).ToJson();
            }
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();

        }

        /// <summary>
        /// 新增群组
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string addGroup(HttpContext context)
        {

            string sql = string.Empty;
            Group group;
            group = JsonHelper.FromJson<Group>(context.Request.Form["data"]);
            //判断是否存在

            object oActive = SqlHelper.ExecuteScalar(string.Format("select active  from SBRM_GROUP where  GROUP_ID ='{0}'  ", group.GROUP_ID), CommandType.Text);

            if (oActive != null && oActive != DBNull.Value)
            {

                string isActive = oActive.ToString();
                //判斷重複 
                if (isActive == "Y")
                {
                    throw new Exception("此群组代码已经存在！");
                }
                else throw new Exception("此群组已被删除，是否还原？");
            }

            //新增

            bool result = true;
            List<string> sqlArr = new List<string>();

            User user = context.Session["user"] as User;
            sql = @"
insert into  sbrm_group
(GROUP_NAME, GROUP_ID , GROUP_DESC,   
UPDATE_USER_KEY,UPDATE_USER_ID,UPDATE_USER_NAME,UPDATE_DATE,ACTIVE ) 
values 
(@GROUP_NAME, @GROUP_ID, @GROUP_DESC, 
@UPDATE_USER_KEY,@UPDATE_USER_ID,@UPDATE_USER_NAME,GETDATE(),'Y') 
select @@identity as ID";

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //插入
                    cmd.CommandText = sql;
                    var parms = new SqlParameter[] {
                        new SqlParameter{ ParameterName="@GROUP_NAME" , Value=group.GROUP_NAME  },
                        new SqlParameter{ ParameterName="@GROUP_ID" , Value= group.GROUP_ID  },
                        new SqlParameter{ ParameterName="@GROUP_DESC" , Value=group.GROUP_DESC },
                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }
                    };
                    cmd.Parameters.AddRange(parms);
                    //cmd.ExecuteNonQuery();
                    group.GROUP_KEY = Convert.ToInt32(cmd.ExecuteScalar());
                    //插入日志
                    insertLog(user, group, "ADD", tx);
                    tx.Commit();
                }
                catch (Exception E)
                {
                    result = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            if (result) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();



        }

        /// <summary>
        /// 编辑群组
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string editGroup(HttpContext context)
        {
            string strSql = string.Empty;
            List<string> sqlArr = new List<string>();
            Group group = JsonHelper.FromJson<Group>(context.Request.Form["data"]);
            int result = 0;
            //判断是否存在

            strSql = "select active  from SBRM_GROUP where GROUP_ID = '{0}' AND GROUP_KEY <> {1} ";
            strSql = string.Format(strSql, group.GROUP_ID, group.GROUP_KEY);
            object oActive = SqlHelper.ExecuteScalar(strSql, CommandType.Text);

            if (oActive != null && oActive != DBNull.Value)
            {
                throw new Exception("此群组代码已经存在！");
            }


            //编辑
            User user = context.Session["user"] as User;

            strSql = @"update sbrm_group 
set GROUP_NAME = @GROUP_NAME,GROUP_ID = @GROUP_ID,GROUP_DESC = @GROUP_DESC,
UPDATE_USER_KEY = @UPDATE_USER_KEY,
UPDATE_USER_ID = @UPDATE_USER_ID,UPDATE_USER_NAME = @UPDATE_USER_NAME,UPDATE_DATE = GETDATE()
where  GROUP_KEY = '{0}' ";
            strSql = string.Format(strSql, group.GROUP_KEY);

            result = SqlHelper.ExecuteNonQuery(strSql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@GROUP_NAME" , Value=string.IsNullOrEmpty(group.GROUP_NAME) ? "" : group.GROUP_NAME  },
                    new SqlParameter{ ParameterName="@GROUP_ID" , Value=string.IsNullOrEmpty(group.GROUP_ID) ? "" : group.GROUP_ID  },
                    new SqlParameter{ ParameterName="@GROUP_DESC" , Value=string.IsNullOrEmpty(group.GROUP_DESC) ? "" : group.GROUP_DESC  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }});

            if (result > 0)
            {
                insertLog(user, group, "EDIT", null);

                return (new Status { Success = true }).ToJson();
            }
            else
            {
                return (new Status { Success = false, Message = "执行异常!" }).ToJson();
            }

        }

        /// <summary>记录日志
        ///  ADD | UPDATE
        /// </summary>
        /// <param name="user"></param>
        /// <param name="group"></param>
        /// <param name="status"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        private int insertLog(User user, Group group, string status, SqlTransaction trans)
        {
            string sql = @"
insert into sbrm_group_log
(GROUP_KEY, GROUP_NAME, GROUP_ID,GROUP_DESC,
UPDATE_USER_KEY, UPDATE_USER_ID, UPDATE_USER_NAME, UPDATE_DATE, STATUS) 
values 
(@GROUP_KEY, @GROUP_NAME, @GROUP_ID, @GROUP_DESC ,
@UPDATE_USER_KEY, @UPDATE_USER_ID, @UPDATE_USER_NAME, GETDATE(), @STATUS)";
            if (trans != null)
                return SqlHelper.ExecuteNonQuery(trans, sql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@GROUP_KEY" , Value= group.GROUP_KEY},
                    new SqlParameter{ ParameterName="@GROUP_NAME" , Value= string.IsNullOrEmpty(group.GROUP_NAME)? "" : group.GROUP_NAME  },
                    new SqlParameter{ ParameterName="@GROUP_ID" , Value=  string.IsNullOrEmpty(group.GROUP_ID)? "" : group.GROUP_ID   },
                    new SqlParameter{ ParameterName="@GROUP_DESC" , Value=  string.IsNullOrEmpty(group.GROUP_DESC)? "" : group.GROUP_DESC   },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  },
                    new SqlParameter{ ParameterName="@STATUS" , Value=status }
                });
            else
                return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@GROUP_KEY" , Value= group.GROUP_KEY},
                    new SqlParameter{ ParameterName="@GROUP_NAME" , Value= string.IsNullOrEmpty(group.GROUP_NAME)? "" : group.GROUP_NAME  },
                    new SqlParameter{ ParameterName="@GROUP_ID" , Value=  string.IsNullOrEmpty(group.GROUP_ID)? "" : group.GROUP_ID   },
                    new SqlParameter{ ParameterName="@GROUP_DESC" , Value=  string.IsNullOrEmpty(group.GROUP_DESC)? "" : group.GROUP_DESC   },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  },
                    new SqlParameter{ ParameterName="@STATUS" , Value=status }
                });
        }

        private DataTable TFuncAction
        {

            set { CacheHelper<DataTable>.Insert(new CacheKey(typeof(DataTable), "action"), value); }
            get { return CacheHelper<DataTable>.GetCache(new CacheKey(typeof(DataTable), "action")); }
        }
        private DataTable TFunction
        {

            set { CacheHelper<DataTable>.Insert(new CacheKey(typeof(DataTable), "function"), value); }
            get { return CacheHelper<DataTable>.GetCache(new CacheKey(typeof(DataTable), "function")); }
        }

        /// <summary>获得登陆用户可见厂别
        /// 
        /// </summary>
        /// <returns></returns>
        public List<Factory> getLoginerFactory()
        {
            User user = HttpContext.Current.Session["user"] as User;
            string strSql = @"select DISTINCT f.* from SBRM_FACTORY f 
join SBRM_GROUP_FACTORY gf on f.FAC_KEY= gf.FAC_KEY  
where gf.GROUP_KEY in (
    SELECT sg.GROUP_KEY FROM SBRM_USER_GROUP sug 
    join SBRM_GROUP sg on sug.group_key = sg.group_key
    WHERE USER_KEY = {0} and sg.ACTIVE = 'Y' 
) order by f.FACTORY_ID";
            strSql = string.Format(strSql, user.USER_KEY);
            //管理员读出所有厂别
            if (isAdmin()) strSql = "select f.* from SBRM_FACTORY f";
            try
            {
                List<Factory> lst = SqlHelper.ExecuteList<Factory>(strSql, CommandType.Text);
                return lst;

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>获得登陆用户拥有的权限
        /// 
        /// </summary>
        /// <returns></returns>
        public List<FuncAction> getLoginerFunAction()
        {
            List<FuncAction> lst = new List<FuncAction>();
            User user = HttpContext.Current.Session["user"] as User;
            string strSql  = @"select distinct fa.ACTION_ID,fa.FUNCTION_KEY
FROM SBRM_FUNC_ACTION fa
join SBRM_GROUP_FUNCTION gf on fa.FUNCTION_KEY = gf.FUNCTION_KEY and  fa.ACTION_ID = gf.ACTION_ID 
where gf.GROUP_KEY  in (
    SELECT sg.GROUP_KEY FROM SBRM_USER_GROUP sug 
    join SBRM_GROUP sg on sug.group_key = sg.group_key
    WHERE   USER_KEY ={0} and sg.ACTIVE = 'Y' 
)  ";
            strSql = string.Format(strSql, user.USER_KEY);
            DataTable tbl = SqlHelper.GetDataSet(strSql).Tables[0];
            foreach (DataRow row in tbl.Rows)
            {
                FuncAction item = new FuncAction
                {
                    ACTION_ID = row["ACTION_ID"].ToString(),
                    FUNC_KEY = Convert.ToInt32(row["FUNCTION_KEY"])
                };
                lst.Add(item);
            }

            return lst;
        }

        /// <summary>所有厂别,当前群组的ENABLE=TRUE
        /// 
        /// </summary>
        /// <param name="groupkey"></param>
        /// <returns></returns>
        public string getGroupFactory(string groupkey)
        {
            DataSet Ds;
            List<GroupFactory> lst = new List<GroupFactory>();
            string sql = string.Format(@"SELECT f.*, gf.GROUP_KEY
                                    FROM   (select DISTINCT GROUP_KEY,FAC_KEY from SBRM_GROUP_FACTORY  where GROUP_KEY ='{0}' ) gf
                                    right join SBRM_FACTORY f  on gf.FAC_KEY=f.FAC_KEY   ", groupkey);
            if (isAdmin() == false)
            {
                User user = HttpContext.Current.Session["user"] as User;
                sql = string.Format(@"SELECT f.*, gf.GROUP_KEY
                                    FROM   (select DISTINCT GROUP_KEY,FAC_KEY from SBRM_GROUP_FACTORY  where GROUP_KEY ='{1}' ) gf
                                    right join  (select DISTINCT sf.* from SBRM_FACTORY sf join SBRM_GROUP_FACTORY sgf on sf.FAC_KEY= sgf.FAC_KEY  where sgf.GROUP_KEY
                                    in (SELECT sg.GROUP_KEY FROM SBRM_USER_GROUP sug join SBRM_GROUP sg on sug.group_key=sg.group_key 
                                     WHERE   USER_KEY ={0} and sg.ACTIVE='Y' ))  f  on gf.FAC_KEY=f.FAC_KEY   ", user.USER_KEY, groupkey);
            }
            try
            {

                Ds = SqlHelper.GetDataSet(sql);
                DataTable dt = Ds.Tables[0];
                DataView view = dt.DefaultView;
                foreach (DataRowView row in view)
                {
                    GroupFactory item = new GroupFactory
                    {
                        FAC_KEY = Convert.ToInt32(row["FAC_KEY"]),
                        FACTORY_ID = row["FACTORY_ID"] == DBNull.Value ? "" : row["FACTORY_ID"].ToString(),
                        FACTORY_NAME = row["FACTORY_NAME"] == DBNull.Value ? "" : row["FACTORY_NAME"].ToString(),
                        ENABLE = row["GROUP_KEY"] == DBNull.Value ? false : true,

                    };

                    lst.Add(item);
                }
                Ds.Dispose();
                return lst.ToJson();



            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();

            }

        }

        /// <summary>
        /// 获得所有父节点ＫＥＹ
        /// </summary>
        /// <param name="funKeysAll"></param>
        /// <param name="key"></param>
        private void getParent(List<string> funKeysAll, string key)
        {
            DataView view = TFunction.DefaultView;
            view.RowFilter = "FUNCTION_KEY=" + key;
            if (view.Count > 0)
            {
                string pid = view[0]["PARENT_KEY"].ToString();
                if (!funKeysAll.Contains(pid)) funKeysAll.Add(pid);
                getParent(funKeysAll, pid);
            }

        }

        /// <summary>
        /// 获得登陆者的权限树
        /// </summary>
        /// <returns></returns>
        private string getLoginerFunkey()
        {
            //选中群组包含哪些菜单
            List<string> funKeys = new List<string>();
            //选中群组包含哪些菜单(包含整棵树）
            List<string> funKeysAll = new List<string>();
            User user = HttpContext.Current.Session["user"] as User;
            //选中群组的操作
            tblLoginerAction = SqlHelper.GetDataSet(string.Format(@"select distinct fa.ACTION_ID,fa.ACTION_NAME,fa.FUNCTION_KEY
                                                    FROM SBRM_FUNC_ACTION   fa
                                                    join SBRM_GROUP_FUNCTION gf on fa.FUNCTION_KEY= gf.FUNCTION_KEY and  fa.ACTION_ID= gf.ACTION_ID 
                                                    where gf.GROUP_KEY  in (SELECT sg.GROUP_KEY FROM SBRM_USER_GROUP sug join SBRM_GROUP sg on sug.group_key=sg.group_key 
              WHERE   USER_KEY ={0} and sg.ACTIVE='Y' )  ", user.USER_KEY)).Tables[0];
            foreach (DataRow row in tblLoginerAction.Rows)
            {
                if (!funKeys.Contains(row["FUNCTION_KEY"].ToString())) funKeys.Add(row["FUNCTION_KEY"].ToString());
            }
            string keystring = string.Empty;

            foreach (string funkey in funKeys)
            {
                //群组权限的整棵树
                getParent(funKeysAll, funkey);
            }
            funKeysAll.AddRange(funKeys);


            foreach (var key in funKeysAll)
            {
                keystring += string.Format("'{0}',", key);

            }
            keystring = keystring.Substring(0, keystring.Length - 1);
            return keystring;


        }

        /// <summary>
        /// 获取群组的菜单
        /// </summary>
        /// <param name="groupkey"></param>
        /// <param name="showAll"></param>
        /// <returns></returns>
        public string getGroupMenu(string groupkey, bool showAll)
        {
            DataSet Ds = new DataSet();
            //所有操作
            if (TFuncAction == null)
            {
                Ds = SqlHelper.GetDataSet("select ACTION_ID,ACTION_NAME,FUNCTION_KEY FROM SBRM_FUNC_ACTION ORDER BY FUNC_ACTION_KEY ");
                TFuncAction = Ds.Tables[0];

            }
            //所有菜单
            if (TFunction == null)
            {
                Ds = SqlHelper.GetDataSet("select FUNC_TYPE,PARENT_KEY,FUNCTION_NAME,FUNCTION_KEY FROM SBRM_FUNCTION order by sequence ");
                TFunction = Ds.Tables[0];

            }

            if (isAdmin() == false) LoginerFunkeys = getLoginerFunkey();

            List<GroupMenu> lst = new List<GroupMenu>();
            try
            {
                //选中群组包含哪些菜单
                List<string> funKeys = new List<string>();
                //选中群组包含哪些菜单(包含整棵树）
                List<string> funKeysAll = new List<string>();

                //选中群组的操作
                tblMyAction = SqlHelper.GetDataSet(string.Format(@"select fa.ACTION_ID,fa.ACTION_NAME,fa.FUNCTION_KEY,gf.GROUP_FUNCTION_KEY
                                                    FROM SBRM_FUNC_ACTION   fa
                                                    join SBRM_GROUP_FUNCTION gf on fa.FUNCTION_KEY= gf.FUNCTION_KEY and  fa.ACTION_ID= gf.ACTION_ID 
                                                    where gf.GROUP_KEY ='{0}' ", groupkey)).Tables[0];
                foreach (DataRow row in tblMyAction.Rows)
                {
                    if (!funKeys.Contains(row["FUNCTION_KEY"].ToString())) funKeys.Add(row["FUNCTION_KEY"].ToString());
                }


                DataTable dt = TFunction;
                string keystring = string.Empty;
                if (!showAll)
                {
                    // 群组权限为空(无包含菜单)直接返回
                    if (funKeys.Count == 0) return lst.ToJson();

                    foreach (string funkey in funKeys)
                    {
                        //群组权限的整棵树
                        getParent(funKeysAll, funkey);
                    }
                    funKeysAll.AddRange(funKeys);


                    foreach (var key in funKeysAll)
                    {
                        keystring += string.Format("'{0}',", key);

                    }
                    keystring = keystring.Substring(0, keystring.Length - 1);
                }
                //if (!showAll) {
                //}
                DataView view = dt.DefaultView;
                view.RowFilter = "PARENT_KEY=0";
                //存所有父节点
                DataView viewF = new DataView(dt);
                viewF.RowFilter = " FUNC_TYPE =0";
                List<string> folder = new List<string>();
                foreach (DataRowView row in viewF)
                {
                    folder.Add(row["FUNCTION_KEY"].ToString());
                }

                foreach (DataRowView row in view)
                {
                    GroupMenu item = new GroupMenu
                    {
                        id = row["FUNCTION_KEY"] == DBNull.Value ? "" : row["FUNCTION_KEY"].ToString(),
                        text = row["FUNCTION_NAME"] == DBNull.Value ? "" : row["FUNCTION_NAME"].ToString(),
                        attributes = new GroupMenuAttr()
                        {
                            ACTION_ID = null,
                            FUNCTION_KEY = 0
                        },
                        children = new List<GroupMenu>()

                    };
                    DataView childview = new DataView(dt);
                    if (showAll)
                    {
                        //编辑状态
                        if (isAdmin() == true) childview.RowFilter = string.Format("PARENT_KEY='{0}' ", row["FUNCTION_KEY"]);
                        else childview.RowFilter = string.Format("PARENT_KEY='{0}' and  FUNCTION_KEY IN ({1}) ", row["FUNCTION_KEY"], LoginerFunkeys);
                    }
                    else childview.RowFilter = string.Format("PARENT_KEY='{0}' and  FUNCTION_KEY IN ({1})", row["FUNCTION_KEY"], keystring);


                    if (childview.Count > 0)
                    {
                        foreach (DataRowView child in childview)
                        {
                            appendGroupMenu(child, dt, item, showAll, groupkey, keystring);
                        }
                    }
                    //根节点存所有目录KEY 
                    item.attributes.ACTION_ID = folder.ToArray().ToJson();
                    lst.Add(item);
                }
                Ds.Dispose();
                return lst.ToJson();



            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();

            }

        }

        private void appendGroupMenu(DataRowView row, DataTable tbl, GroupMenu parent, 
            bool showAll, string GroupKey, string keystring)
        {

            DataView view = new DataView(tbl);
            if (showAll)
            {
                //编辑状态
                if (isAdmin() == true) view.RowFilter = string.Format("PARENT_KEY='{0}' ", row["FUNCTION_KEY"]);
                else view.RowFilter = string.Format("PARENT_KEY='{0}' and  FUNCTION_KEY IN ({1}) ", row["FUNCTION_KEY"], LoginerFunkeys);
            }
            else view.RowFilter = string.Format("PARENT_KEY='{0}' and FUNCTION_KEY IN ({1})", row["FUNCTION_KEY"], keystring);
            //  view.Sort = "SEQUENCE ";
            GroupMenu item = new GroupMenu
            {
                id = row["FUNCTION_KEY"] == DBNull.Value ? "" : row["FUNCTION_KEY"].ToString(),
                text = row["FUNCTION_NAME"] == DBNull.Value ? "" : row["FUNCTION_NAME"].ToString(),

                attributes = new GroupMenuAttr()
                {
                    ACTION_ID = null,
                    FUNCTION_KEY = 0
                },
                children = new List<GroupMenu>()

            };

            if (view.Count > 0)
            {
                foreach (DataRowView child in view)
                {
                    appendGroupMenu(child, tbl, item, showAll, GroupKey, keystring);
                }

            }
            else
            {
                //没有子节点,给菜单加ACTION节点
                if (row["FUNC_TYPE"].ToString() == "1")
                {
                    if (showAll)
                    {
                        //编辑状态
                        //ADMIN 显示所有ACTION ,否则显示登陆者的所有ACTION 
                        DataView viewAction;
                        if (isAdmin()) viewAction = new DataView(TFuncAction);
                        else viewAction = new DataView(tblLoginerAction);
                        DataView viewMyAction2 = new DataView(tblMyAction);
                        viewAction.RowFilter = string.Format("FUNCTION_KEY={0}", row["FUNCTION_KEY"]);
                        foreach (DataRowView vRow in viewAction)
                        {
                            GroupMenu itemAction = new GroupMenu
                            {
                                id = vRow["ACTION_ID"] == DBNull.Value ? "" : vRow["ACTION_ID"].ToString(),
                                text = vRow["ACTION_NAME"] == DBNull.Value ? "" : vRow["ACTION_NAME"].ToString(),

                                attributes = new GroupMenuAttr()
                                {

                                    ACTION_ID = vRow["ACTION_ID"].ToString(),
                                    FUNCTION_KEY = Convert.ToInt32(vRow["FUNCTION_KEY"].ToString())
                                },

                                children = new List<GroupMenu>()

                            };
                            //把现有权限的GROUP_FUNCTION_KEY加上去
                            viewMyAction2.RowFilter = string.Format("FUNCTION_KEY={0} and ACTION_ID='{1}'", itemAction.attributes.FUNCTION_KEY, itemAction.attributes.ACTION_ID);
                            if (viewMyAction2.Count > 0)
                            {
                                itemAction.Check = true;
                                itemAction.attributes.GROUP_FUNCTION_KEY = viewMyAction2[0]["GROUP_FUNCTION_KEY"].ToString();
                            }
                            item.children.Add(itemAction);

                        }

                    }
                    else
                    {
                        //显示群组的ACTION
                        DataView viewMyAction = new DataView(tblMyAction);
                        viewMyAction.RowFilter = string.Format("FUNCTION_KEY={0}", row["FUNCTION_KEY"]);
                        foreach (DataRowView vRow in viewMyAction)
                        {
                            GroupMenu itemAction = new GroupMenu
                            {
                                id = vRow["ACTION_ID"] == DBNull.Value ? "" : vRow["ACTION_ID"].ToString(),
                                text = vRow["ACTION_NAME"] == DBNull.Value ? "" : vRow["ACTION_NAME"].ToString(),

                                attributes = new GroupMenuAttr()
                                {
                                    //现有权限才有GROUP_FUNCTION_KEY
                                    GROUP_FUNCTION_KEY = vRow["GROUP_FUNCTION_KEY"].ToString(),
                                    ACTION_ID = vRow["ACTION_ID"].ToString(),
                                    FUNCTION_KEY = Convert.ToInt32(vRow["FUNCTION_KEY"].ToString())
                                },
                                children = new List<GroupMenu>()

                            };
                            item.children.Add(itemAction);

                        }


                    }


                }



            }
            parent.children.Add(item);


        }

        /// <summary>新增& 删除群组菜单并记录日志
        ///
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string editGroupMenu(HttpContext context)
        {
            string strSql = "";
            List<string> sql = new List<string>();
            List<string> sqlAdd = new List<string>();
            string Msg = string.Empty;
            User user = context.Session["user"] as User;
            int result;

            GroupMenuList gMenus = JsonHelper.FromJson<GroupMenuList>(context.Request.Form["data"]);
            int len = gMenus.menus.Count;
            strSql = @"select fa.ACTION_ID,fa.ACTION_NAME,fa.FUNCTION_KEY,gf.GROUP_FUNCTION_KEY
FROM SBRM_FUNC_ACTION   fa
join SBRM_GROUP_FUNCTION gf on fa.FUNCTION_KEY= gf.FUNCTION_KEY  and  fa.ACTION_ID= gf.ACTION_ID  
where gf.GROUP_KEY ='{0}' ";
            strSql = string.Format(strSql, gMenus.GROUP_KEY);
            tblMyAction = SqlHelper.GetDataSet(strSql).Tables[0];

            strSql = @"select f.* 
FROM SBRM_FACTORY   f
join SBRM_GROUP_FACTORY gf on f.FAC_KEY= gf.FAC_KEY
where gf.GROUP_KEY = '{0}' ";
            strSql = string.Format(strSql, gMenus.GROUP_KEY);
            DataTable tblGroupFac = SqlHelper.GetDataSet(strSql).Tables[0];

            //群组厂别
            int lenFac = gMenus.factorys.Count;
            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    #region save factory
                    if (lenFac == 0)
                    {
                        //删除所有权限
                        strSql = "delete from SBRM_GROUP_FACTORY where GROUP_KEY = '{0}' ";
                        strSql = string.Format(strSql, gMenus.GROUP_KEY);
                        result = SqlHelper.ExecuteNonQuery(tx, strSql, CommandType.Text);
                        //日志
                        foreach (DataRow r in tblGroupFac.Rows)
                        {
                            result = insertGroupFacLog(user, new GroupFactory { FAC_KEY = Convert.ToInt32(r["FAC_KEY"]), FACTORY_NAME = r["FACTORY_NAME"].ToString() },
                                        gMenus.GROUP_KEY, "DELETE", tx);
                        }

                    }
                    else if (lenFac > 0)
                    {
                        foreach (GroupFactory fac in gMenus.factorys)
                        {
                            //只新增原来没有的权限
                            if (fac.ENABLE == false)
                            {
                                strSql = @"insert into  SBRM_GROUP_FACTORY
(GROUP_KEY, FAC_KEY, FACTORY_NAME, UPDATE_USER_KEY, UPDATE_USER_ID, UPDATE_USER_NAME, UPDATE_DATE)  
values
('{0}', '{1}', '{2}', @UPDATE_USER_KEY, @UPDATE_USER_ID, @UPDATE_USER_NAME, GETDATE()) ";
                                strSql = string.Format(strSql, gMenus.GROUP_KEY, fac.FAC_KEY, fac.FACTORY_NAME);
                                var parms = new SqlParameter[] {
                                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }
                                };
                                result = SqlHelper.ExecuteNonQuery(tx, strSql, CommandType.Text, parms);
                                //记录日志
                                result = insertGroupFacLog(user, fac, gMenus.GROUP_KEY, "ADD", tx);
                            }

                        }
                        //删除被取消的权限,并记录日志
                        foreach (DataRow r in tblGroupFac.Rows)
                        {
                            int FacKey = Convert.ToInt32(r["FAC_KEY"]);
                            int index = gMenus.factorys.FindIndex(item => item.FAC_KEY == FacKey);
                            if (index < 0)
                            {
                                strSql = "delete from SBRM_GROUP_FACTORY where GROUP_KEY = '{0}' and FAC_KEY = '{1}' ";
                                strSql = string.Format(strSql, gMenus.GROUP_KEY, FacKey);
                                result = SqlHelper.ExecuteNonQuery(tx, strSql, CommandType.Text);
                                result = insertGroupFacLog(user, new GroupFactory { FAC_KEY = FacKey, FACTORY_NAME = r["FACTORY_NAME"].ToString() }, gMenus.GROUP_KEY, "DELETE", tx);
                            }

                        }

                    }
                    #endregion

                    #region save menu
                    if (len == 0)
                    {
                        //删除所有权限
                        strSql = "delete from SBRM_GROUP_FUNCTION where GROUP_KEY = '{0}' ";
                        strSql = string.Format(strSql, gMenus.GROUP_KEY);
                        result = SqlHelper.ExecuteNonQuery(tx, strSql, CommandType.Text);
                        //日志
                        foreach (DataRow r in tblMyAction.Rows)
                        {
                            result = insertGroupMenuLog(user, new GroupMenuAttr { ACTION_ID = r["ACTION_ID"].ToString(), FUNCTION_KEY = Convert.ToInt32(r["FUNCTION_KEY"]) },
                             r["GROUP_FUNCTION_KEY"].ToString(), gMenus.GROUP_KEY, "DELETE", tx);
                        }

                    }
                    else if (len > 0)
                    {
                        foreach (GroupMenuAttr menu in gMenus.menus)
                        {
                            //只新增原来没有的权限
                            if (menu.GROUP_FUNCTION_KEY == null)
                            {
                                //根节点过滤掉
                                if (menu.FUNCTION_KEY != 0)
                                {
                                    var parms = new SqlParameter[] {
                                        new SqlParameter{ ParameterName="@GROUP_KEY" , Value= gMenus.GROUP_KEY  },
                                        new SqlParameter{ ParameterName="@FUNCTION_KEY" , Value= menu.FUNCTION_KEY },
                                        new SqlParameter{ ParameterName="@ACTION_ID" , Value= menu.ACTION_ID },
                                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                                        new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }
                                    };
                                    strSql = @"insert into  SBRM_GROUP_FUNCTION
(GROUP_KEY, FUNCTION_KEY, ACTION_ID, UPDATE_USER_KEY, UPDATE_USER_ID, UPDATE_USER_NAME, UPDATE_DATE)
values
(@GROUP_KEY, @FUNCTION_KEY, @ACTION_ID, @UPDATE_USER_KEY, @UPDATE_USER_ID, @UPDATE_USER_NAME, GETDATE() )
select @@identity as ID ";
                                    object newID = SqlHelper.ExecuteScalar(tx, CommandType.Text, strSql, parms);
                                    //记录日志
                                    result = insertGroupMenuLog(user, menu, newID.ToString(), gMenus.GROUP_KEY, "ADD", tx);
                                }
                            }

                        }

                        //删除被取消的权限,并记录日志
                        foreach (DataRow r in tblMyAction.Rows)
                        {
                            string groupFunKey = r["GROUP_FUNCTION_KEY"].ToString();
                            int index = gMenus.menus.FindIndex(item => item.GROUP_FUNCTION_KEY == groupFunKey);
                            if (index < 0)
                            {
                                strSql = "delete from SBRM_GROUP_FUNCTION where GROUP_FUNCTION_KEY = '{0}' ";
                                strSql = string.Format(strSql, groupFunKey);
                                result = SqlHelper.ExecuteNonQuery(tx, strSql, CommandType.Text);
                                result = insertGroupMenuLog(user, new GroupMenuAttr { ACTION_ID = r["ACTION_ID"].ToString(), FUNCTION_KEY = Convert.ToInt32(r["FUNCTION_KEY"]) },
                              groupFunKey, gMenus.GROUP_KEY, "DELETE", tx);
                            }

                        }

                    }
                    #endregion

                    tx.Commit();

                }
                catch (Exception E)
                {
                    tx.Rollback();
                    Msg = E.Message;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();

                }
            }

            if (string.IsNullOrEmpty(Msg)) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();


        }

        private int insertGroupMenuLog(User user, GroupMenuAttr menu,
            string GROUP_FUNCTION_KEY, string GROUP_KEY, string status,
            SqlTransaction trans)
        {
            string sql = string.Format(@"insert into  sbrm_group_function_log
(GROUP_FUNCTION_KEY,GROUP_KEY,FUNCTION_KEY, ACTION_ID, UPDATE_USER_KEY,UPDATE_USER_ID,UPDATE_USER_NAME ,UPDATE_DATE, STATUS) 
values 
({0},{1},{2},'{3}', @UPDATE_USER_KEY,@UPDATE_USER_ID,@UPDATE_USER_NAME ,GETDATE(),'{4}')",
                                GROUP_FUNCTION_KEY, GROUP_KEY, menu.FUNCTION_KEY, menu.ACTION_ID, status);
            if (trans != null)
                return SqlHelper.ExecuteNonQuery(trans, sql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }
                });
            else
                return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }
                });
        }

        private int insertGroupFacLog(User user, GroupFactory fac, 
            string GROUP_KEY, string status, SqlTransaction trans)
        {
            string sql = string.Format(@"insert into  sbrm_group_factory_log
(FAC_KEY,GROUP_KEY,FACTORY_NAME, UPDATE_USER_KEY,UPDATE_USER_ID,UPDATE_USER_NAME ,UPDATE_DATE, STATUS) 
values 
({0},{1},'{2}', @UPDATE_USER_KEY,@UPDATE_USER_ID,@UPDATE_USER_NAME ,GETDATE(),'{3}')",
                                fac.FAC_KEY, GROUP_KEY, fac.FACTORY_NAME, status);
            if (trans != null) 
                return SqlHelper.ExecuteNonQuery(trans, sql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }
                });
            else 
                return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }
                });
        }

    }

}
